package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.AttackedAttackerIpDetail;
import com.youan.log.modules.audit.service.IAttackedAttackerIpDetailService;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 关于被攻击ip 对应每个攻击者的详细信息
 */
public class AttackedAttackerIpDetailDataExtract {

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private IAttackedAttackerIpDetailService attackedAttackerIpDetailService;
    private JdbcTemplate jdbcTemplate;
    private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    List<AttackedAttackerIpDetail> needAddToDB = new ArrayList<>();
    List<AttackedAttackerIpDetail> needUpdateToDB = new ArrayList<>();
    private int BATCH_SIZE = 5000;

    public AttackedAttackerIpDetailDataExtract(String date, ThreadTaskProgressor taskProgressor) {
        this.date = date;
        this.taskProgressor = taskProgressor;
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        attackedAttackerIpDetailService = SpringUtil.getBean(IAttackedAttackerIpDetailService.class);
        taskProgressor.updateProgressTotal(taskProgressor.getProgressTotalNum() + getCount());
    }

//    SELECT
//    dest_ip attacked_ip,
//    source_ip attacker_ip,
//    dest_country attacked_country,
//    dest_province attacked_province,
//    dest_city attacked_city,
//    source_country attacker_country,
//    source_province attacker_province,
//    source_city attacker_city,
//    visit_number attacked_num,
//	0 login_success_num
//            FROM
//    log_statistical a
//    WHERE
//    issue_type IN ( '10050001485', '10050001949', '10050001950', '10050001994', '10050001999' )
//    AND visit_date = '2021-04-05'
//    AND a.dest_ip IN ( SELECT attacked_ip FROM log_attacked_analysis WHERE date = '2021-04-05' )
//    AND NOT EXISTS ( SELECT * FROM log_attacked_attacker_ip_detail detail WHERE detail.attacked_ip = a.dest_ip AND detail.attacker_ip = a.source_ip )
//
//    SELECT
//    source_ip attacked_ip,
//    dest_ip attacker_ip,
//    source_country attacked_country,
//    source_province attacked_province,
//    source_city attacked_city,
//    dest_country attacker_country,
//    dest_province attacker_province,
//    dest_city attacker_city,
//    visit_number attacked_num,
//            CASE
//    issue_type
//    WHEN '10050001476' THEN
//            visit_number
//    WHEN '10050001084' THEN
//	0
//    END AS login_success_num
//            FROM
//    log_statistical a
//    WHERE
//    issue_type IN ( '10050001084', '10050001476' )
//    AND visit_date = '2021-04-05'
//    AND a.source_ip IN ( SELECT attacked_ip FROM log_attacked_analysis WHERE date = '2021-04-05' )
//    AND NOT EXISTS ( SELECT * FROM log_attacked_attacker_ip_detail detail WHERE detail.attacked_ip = a.source_ip AND detail.attacker_ip = a.dest_ip )

    public void extract() {

        // 分两步生成到数据库，原来的union速度太慢
        String sql = "SELECT\n" +
                "  dest_ip attacked_ip,\n" +
                "  source_ip attacker_ip,\n" +
                "  dest_country attacked_country,\n" +
                "  dest_province attacked_province,\n" +
                "  dest_city attacked_city,\n" +
                "  source_country attacker_country,\n" +
                "  source_province attacker_province,\n" +
                "  source_city attacker_city,\n" +
                "  0 attacked_num,\n" +
                "  0 login_success_num\n" +
                "FROM\n" +
                "  log_statistical a,\n" +
                "  log_attack_analysis b,\n" +
                "  log_attacked_analysis c\n" +
                "WHERE\n" +
                "  a.source_ip = b.attacker_ip\n" +
                "AND b.date = '" + this.date + "'\n" +
                "AND visit_date = '" + this.date + "'\n" +
                "AND a.dest_ip = c.attacked_ip\n" +
                "AND c.date = '" + this.date + "'\n" +
                "AND NOT EXISTS (\n" +
                "  SELECT\n" +
                "    *\n" +
                "  FROM\n" +
                "    log_attacked_attacker_ip_detail detail\n" +
                "  WHERE\n" +
                "    detail.attacked_ip = a.dest_ip\n" +
                "  AND detail.attacker_ip = a.source_ip\n" +
                ")\n" +
                "GROUP BY\n" +
                "  source_ip,\n" +
                "  dest_ip,\n" +
                "  source_country,\n" +
                "  source_province,\n" +
                "  source_city,\n" +
                "  dest_country,\n" +
                "  dest_province,\n" +
                "  dest_city\n";

        buildAndSaveEntity(sql);

        sql = "SELECT\n" +
                " source_ip attacked_ip,\n" +
                " dest_ip attacker_ip,\n" +
                " source_country attacked_country,\n" +
                " source_province attacked_province,\n" +
                " source_city attacked_city,\n" +
                " dest_country attacker_country,\n" +
                " dest_province attacker_province,\n" +
                " dest_city attacker_city,\n" +
                " 0 attacked_num,\n" +
                " 0 login_success_num\n" +
                "FROM\n" +
                " log_statistical a,\n" +
                " log_attack_analysis b,\n" +
                " log_attacked_analysis c\n" +
                "WHERE\n" +
                " a.dest_ip = b.attacker_ip\n" +
                "AND a.source_ip = c.attacked_ip\n" +
                "AND b.date = '" + this.date + "'\n" +
                "AND c.date = '" + this.date + "'\n" +
                "AND visit_date = '" + this.date + "'\n" +
                "AND NOT EXISTS (\n" +
                "  SELECT\n" +
                "    *\n" +
                "  FROM\n" +
                "    log_attacked_attacker_ip_detail detail\n" +
                "  WHERE\n" +
                "    detail.attacked_ip = a.source_ip\n" +
                "  AND detail.attacker_ip = a.dest_ip\n" +
                ")\n" +
                "GROUP BY\n" +
                " source_ip,\n" +
                " dest_ip,\n" +
                " source_country,\n" +
                " source_province,\n" +
                " source_city,\n" +
                " dest_country,\n" +
                " dest_province,\n" +
                " dest_city";

        buildAndSaveEntity(sql);


        // 通过sql一次性更新 首次被攻击时间和最近被攻击时间
        String updateSql = "UPDATE log_attacked_attacker_ip_detail detail\n" +
                "SET detail.first_attacked_time = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   MIN(a.first_occurrence_time)\n" +
                "  FROM\n" +
                "   log_statistical a\n" +
                "  WHERE\n" +
                "   a.source_ip = detail.attacked_ip\n" +
                "  AND a.dest_ip = detail.attacker_ip\n" +
                "  AND visit_date = '" + this.date + "'\n" +
                " ),\n" +
                " '2999-10-10'\n" +
                ")\n" +
                "WHERE\n" +
                " detail.first_attacked_time IS NULL";
        jdbcTemplate.execute(updateSql);
        updateSql = "UPDATE log_attacked_attacker_ip_detail detail\n" +
                "SET detail.first_attacked_time = LEAST(\n" +
                " IFNULL(\n" +
                "  (\n" +
                "   SELECT\n" +
                "    MIN(a.first_occurrence_time)\n" +
                "   FROM\n" +
                "    log_statistical a\n" +
                "   WHERE\n" +
                "    a.dest_ip = detail.attacked_ip\n" +
                "   AND a.source_ip = detail.attacker_ip\n" +
                "   AND visit_date = '" + this.date + "'\n" +
                "  ),\n" +
                "  '2999-10-10'\n" +
                " ),\n" +
                " detail.first_attacked_time\n" +
                ")\n" +
                "WHERE\n" +
                " SUBSTRING(first_attacked_time FROM 1 FOR 10) >= '" + this.date + "'";
        jdbcTemplate.execute(updateSql);

        updateSql = "UPDATE log_attacked_attacker_ip_detail detail\n" +
                "SET detail.latest_attacked_time = (\n" +
                " IFNULL(\n" +
                "  (\n" +
                "   SELECT\n" +
                "    MAX(a.latest_occurrence_time)\n" +
                "   FROM\n" +
                "    log_statistical a\n" +
                "   WHERE\n" +
                "    a.source_ip = detail.attacked_ip\n" +
                "   AND a.dest_ip = detail.attacker_ip\n" +
                "   AND visit_date = '" + this.date + "'\n" +
                "  ),\n" +
                "  '0000-00-00'\n" +
                " )\n" +
                ")\n" +
                "WHERE\n" +
                " detail.latest_attacked_time IS NULL";
        jdbcTemplate.execute(updateSql);

        updateSql = "UPDATE log_attacked_attacker_ip_detail detail\n" +
                "SET detail.latest_attacked_time = GREATEST(\n" +
                " IFNULL(\n" +
                "  (\n" +
                "   SELECT\n" +
                "    MAX(a.latest_occurrence_time)\n" +
                "   FROM\n" +
                "    log_statistical a\n" +
                "   WHERE\n" +
                "    a.dest_ip = detail.attacked_ip\n" +
                "   AND a.source_ip = detail.attacker_ip\n" +
                "   AND visit_date = '" + this.date + "'\n" +
                "  ),\n" +
                "  '0000-00-00'\n" +
                " ),\n" +
                " detail.latest_attacked_time\n" +
                ")\n" +
                "WHERE\n" +
                " SUBSTRING(latest_attacked_time FROM 1 FOR 10) <= '" + this.date + "'";
        jdbcTemplate.execute(updateSql);

        // 在插入当天新数据之后更新一下当前表里的数据的被攻击次数和攻击成功次数
        String updateNumSql = "UPDATE log_attacked_attacker_ip_detail a\n" +
                "SET attacked_num = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(visit_number)\n" +
                "  FROM\n" +
                "   log_statistical b\n" +
                "  WHERE\n" +
                "  visit_date = '" + this.date + "'\n" +
                "  AND dest_ip = a.attacked_ip\n" +
                "  AND source_ip = a.attacker_ip\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_num";
        jdbcTemplate.execute(updateNumSql);

        updateNumSql = "UPDATE log_attacked_attacker_ip_detail a\n" +
                "SET attacked_num = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(visit_number)\n" +
                "  FROM\n" +
                "   log_statistical b\n" +
                "  WHERE\n" +
                "  visit_date = '" + this.date + "'\n" +
                "  AND source_ip = a.attacked_ip\n" +
                "  AND dest_ip = a.attacker_ip\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_num\n";
        jdbcTemplate.execute(updateNumSql);

        updateNumSql = "UPDATE log_attacked_attacker_ip_detail a\n" +
                "SET login_success_num = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(visit_number)\n" +
                "  FROM\n" +
                "   log_statistical b\n" +
                "  WHERE\n" +
                "   issue_type = '10050001476'\n" +
                "  AND visit_date = '" + this.date + "'\n" +
                "  AND source_ip = a.attacked_ip\n" +
                "  AND dest_ip = a.attacker_ip\n" +
                " ),\n" +
                " 0\n" +
                ") + login_success_num\n";
        jdbcTemplate.execute(updateNumSql);
    }

    private void buildAndSaveEntity(String sql) {

        needAddToDB = new ArrayList<>();

        jdbcTemplate.query(sql, rs -> {

            AttackedAttackerIpDetail item = new AttackedAttackerIpDetail();
            item.setAttackedIp(rs.getString("attacked_ip"));
            item.setAttackerIp(rs.getString("attacker_ip"));
            item.setAttackedCountry(rs.getString("attacked_country"));
            item.setAttackedProvince(rs.getString("attacked_province"));
            item.setAttackedCity(rs.getString("attacked_city"));
            item.setAttackerCountry(rs.getString("attacker_country"));
            item.setAttackerProvince(rs.getString("attacker_province"));
            item.setAttackerCity(rs.getString("attacker_city"));
            item.setAttackedNum(rs.getInt("attacked_num"));
            item.setLoginSuccessNum(rs.getInt("login_success_num"));
            needAddToDB.add(item);

            if(needAddToDB.size() >= BATCH_SIZE) {
                attackedAttackerIpDetailService.saveBatch(needAddToDB);
                taskProgressor.addProgressNum(needAddToDB.size());
                needAddToDB = new ArrayList<>();
            }
        });
        attackedAttackerIpDetailService.saveBatch(needAddToDB);
        taskProgressor.addProgressNum(needAddToDB.size());
    }

    private int getCount() {
        String sql = "SELECT\n" +
                " count(1)\n" +
                "FROM\n" +
                "  (\n" +
                "    SELECT\n" +
                "      dest_ip attacked_ip,\n" +
                "      source_ip attacker_ip,\n" +
                "      dest_country attacked_country,\n" +
                "      dest_province attacked_province,\n" +
                "      dest_city attacked_city,\n" +
                "      source_country attacker_country,\n" +
                "      source_province attacker_province,\n" +
                "      source_city attacker_city\n" +
                "    FROM\n" +
                "      log_statistical\n" +
                "    WHERE\n" +
                "      issue_type IN (\n" +
                "        '10050001485',\n" +
                "        '10050001949',\n" +
                "        '10050001950',\n" +
                "        '10050001994',\n" +
                "        '10050001999'\n" +
                "      )\n" +
                "    AND visit_date = '" + this.date + "'\n" +
                "    UNION\n" +
                "      SELECT\n" +
                "        source_ip,\n" +
                "        dest_ip,\n" +
                "        source_country,\n" +
                "        source_province,\n" +
                "        source_city,\n" +
                "        dest_country,\n" +
                "        dest_province,\n" +
                "        dest_city\n" +
                "      FROM\n" +
                "        log_statistical\n" +
                "      WHERE\n" +
                "        issue_type IN (\n" +
                "          '10050001084',\n" +
                "          '10050001476'\n" +
                "        )\n" +
                "      AND visit_date = '" + this.date + "'\n" +
                "  ) a\n" +
                "WHERE\n" +
                "  a.attacked_ip IN (\n" +
                "    SELECT\n" +
                "      attacked_ip\n" +
                "    FROM\n" +
                "      log_attacked_analysis\n" +
                "      WHERE date = '" + this.date + "' \n" +
                "  )\n" +
                "ORDER BY\n" +
                "  attacked_ip";

        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

}
